/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tuitionmanagement.DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Vector;
import javax.swing.DefaultComboBoxModel;
import javax.swing.JComboBox;
import tuitionmanagement.entity.StaticticEntity;
import tuitionmanagement.util.DBUtil;

/**
 *
 * @author XUAN
 */
public class StaticticsDao {

    Connection con;
    private ResultSet rs;
    private PreparedStatement pst;
    private String lastError;
    private final String sql_selectClassName = "select ClassName from Class";
    private final String sql_selectStExam = "select StudentId,StudentName,ClassName,sum(MoneyTotal)as TotalMoney,sum([Money]) as PaidMoney,sum([Money])-sum(MoneyTotal) as 'Balance' from studentOrder where ClassName = ? and (FeeDate between ? and ?) group by StudentId,StudentName,ClassName having (sum([Money])-sum(MoneyTotal))>=0";
    private final String sql_selectStNoExam = "select StudentId,StudentName,ClassName,sum(MoneyTotal)as TotalMoney,sum([Money]) as PaidMoney,sum([Money])-sum(MoneyTotal) as 'Balance' from studentOrder where ClassName = ? and (FeeDate between ? and ?) group by StudentId,StudentName,ClassName having (sum([Money])-sum(MoneyTotal))<0";
    private final String sql_selectStatictic = "select Invoice.InvoiceId,StudentName, MoneyTotal,[Money] as 'PaidMoney',(MoneyTotal - [Money]) as 'Dept'from Invoice join FeeLevel on Invoice.FeeLevelId = FeeLevel.FeeLevelId join [Order] on Invoice.InvoiceId = [Order].InvoiceId join Student on Student.StudentId = Invoice.StudentId where FeeDate between ? and ?";

    public void getcmbClassName(JComboBox cmbClassName, String className) {
        Vector vt = new Vector();
        vt.add("");
        CallableStatement cs = null;
        ResultSet rst = null;
        try {
            con = DBUtil.getConn();
            cs = con.prepareCall(sql_selectClassName);
            rst = cs.executeQuery();
            if (rst == null) {
                System.out.println("Not Data");
            } else {
                while (rst.next()) {
//                    vt.add(rst.getString(OrderId));
                    vt.add(rst.getString(className));
                }
            }
            cmbClassName.setModel(new DefaultComboBoxModel(vt));
            DBUtil.closeConn(con);
        } catch (Exception ex) {
            System.out.println("Error Add, GetCmbCourseName " + ex.getMessage());
        }
    }

    public ArrayList<StaticticEntity> getStaticticExamByClassNameDate(String classname, Date searchFrom, Date searchTo) {
        ArrayList<StaticticEntity> list = new ArrayList<StaticticEntity>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_selectStExam);
            pst.setString(1, classname);
            pst.setDate(2, new java.sql.Date(searchFrom.getTime()));
            pst.setDate(3, new java.sql.Date(searchTo.getTime()));
            rs = pst.executeQuery();
            while (rs.next()) {
                StaticticEntity sta = new StaticticEntity();
                sta.setStudentId(rs.getInt("StudentId"));
                sta.setStudentName(rs.getString("StudentName"));
                sta.setTotalMoney(rs.getFloat("TotalMoney"));
                sta.setPaidMoney(rs.getFloat("PaidMoney"));
                sta.setBanlance(rs.getFloat("Balance"));

                list.add(sta);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }

    public ArrayList<StaticticEntity> getStaticticNoExamByClassNameDate(String classname, Date searchFrom, Date searchTo) {
        ArrayList<StaticticEntity> list = new ArrayList<StaticticEntity>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_selectStNoExam);
            pst.setString(1, classname);
            pst.setDate(2, new java.sql.Date(searchFrom.getTime()));
            pst.setDate(3, new java.sql.Date(searchTo.getTime()));
            rs = pst.executeQuery();
            while (rs.next()) {
                StaticticEntity sta = new StaticticEntity();
                sta.setStudentId(rs.getInt("StudentId"));
                sta.setStudentName(rs.getString("StudentName"));
                sta.setTotalMoney(rs.getFloat("TotalMoney"));
                sta.setPaidMoney(rs.getFloat("PaidMoney"));
                sta.setBanlance(rs.getFloat("Balance"));

                list.add(sta);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;
    }

    public ArrayList<StaticticEntity> getStaticticByDate(Date searchFrom, Date searchTo) {
        ArrayList<StaticticEntity> list = new ArrayList<StaticticEntity>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_selectStatictic);
            pst.setDate(1, new java.sql.Date(searchFrom.getTime()));
            pst.setDate(2, new java.sql.Date(searchTo.getTime()));
            rs = pst.executeQuery();
            while (rs.next()) {
                StaticticEntity sta = new StaticticEntity();
                sta.setStudentId(rs.getInt("InvoiceId"));
                sta.setStudentName(rs.getString("StudentName"));
                sta.setTotalMoney(rs.getFloat("MoneyTotal"));
                sta.setPaidMoney(rs.getFloat("PaidMoney"));
                sta.setBanlance(rs.getFloat("Dept"));

                list.add(sta);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;
    }
}
